package avicit.bdp.dcs.utils;

import avicit.bdp.common.utils.CheckParaUtils;
import avicit.bdp.core.constant.Constants;
import avicit.bdp.dcs.semistructure.dto.TemplateFieldDTO;
import avicit.bdp.dcs.stream.dto.MeasureParaDto;
import avicit.bdp.dcs.stream.dto.MsgTemplateDto;
import avicit.bdp.dcs.tools.enums.MeasureParaTypeEnum;
import avicit.bdp.dcs.tools.enums.TemplateFileTitleEnum;
import avicit.platform6.commons.utils.ComUtil;
import avicit.platform6.core.exception.BusinessException;
import com.alibaba.fastjson2.JSONObject;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @金航数码科技有限责任公司
 * @作者：developer
 * @邮箱：developer@avic-digital.com
 * @创建时间：2020-10-16
 * @类说明：读取Excel消息模板文件
 * @修改记录：
 */
public class MsgTemplateExcelUtils {

  private static final Logger logger = LoggerFactory.getLogger(MsgTemplateExcelUtils.class);

  /** 工作表中合并单元格 */
  private static List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();

  /**
   * 从Excel中解析设备信息
   *
   * @param inputStream，输入流
   * @param fileName，模板文件名称
   * @return
   */
  public static List<MsgTemplateDto> parseDevices(String fileName, InputStream inputStream) {
    List<MsgTemplateDto> devices = new ArrayList<>();

    // step1:定义工作簿，读取模板文件
    Workbook workbook = getWorkBook(fileName, inputStream);

    // step2:定义工作表
    for (int index = 0; index < workbook.getNumberOfSheets(); index++) {
      Sheet currSheet = getSheetByIndex(workbook, index);
      devices.addAll(readDeviceInfoFromSheet(fileName, currSheet));
    }

    return devices;
  }

  /**
   * 从文件中解析设备信息
   *
   * @param fileName
   * @return
   */
  public static List<MsgTemplateDto> parseDeviceInfo(String fileName) {
    List<MsgTemplateDto> devices = new ArrayList<>();

    // step1:定义工作簿，读取模板文件
    Workbook workbook = getWorkBookFromFile(fileName);

    // step2:定义工作表
    for (int index = 0; index < workbook.getNumberOfSheets(); index++) {
      Sheet currSheet = getSheetByIndex(workbook, index);
      devices.addAll(readDeviceInfoFromSheet(fileName, currSheet));
    }
    return devices;
  }

  /**
   * 从模板文件中获取device信息
   *
   * @param sheet
   * @return
   */
  private static List<MsgTemplateDto> readDeviceInfoFromSheet(String fileName, Sheet sheet) {
    List<MsgTemplateDto> devices = new ArrayList<>();

    // step1:从工作表中获取合并单元格
    getCombineCell(sheet);

    // step2:判断标题行是否合法
    // 获取工作表第一行下标
    int firstRowNum = sheet.getFirstRowNum();
    // 获取工作表最后一行下标
    int lastRowNum = sheet.getLastRowNum();
    checkTitleRowValidity(fileName, sheet, firstRowNum);

    // step3:循环获取每行的数据
    for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
      // 跳过空行
      Row row = sheet.getRow(rowIndex);
      if (isRowEmpty(row)) {
        continue;
      }

      // step4:获取模板基本信息，前10列是设备基本信息
      MsgTemplateDto msgTemplateDto =
          getDeviceBaseInfoFromRow(sheet, row, rowIndex, devices, fileName);

      // step5:获取模板中测量参数信息，后面1列是参数信息
      List<MeasureParaDto> measureParaDtoList =
          getMeasureParaFrowRow(sheet, row, rowIndex, fileName, msgTemplateDto.getDeviceId());

      // step6:设置字段paras（持久化参数信息）、measureParaDtoList（返回给VUE前端，用于解析结果展示）
      msgTemplateDto.setMeasureParaDtoList(measureParaDtoList);
      String newParas = JSONObject.toJSONString(measureParaDtoList);
      msgTemplateDto.setParas(newParas);
    }

    return devices;
  }

  /**
   * 检查模板文件标题行合法性
   *
   * @param sheet
   * @param firstRowNum
   */
  private static void checkTitleRowValidity(String fileName, Sheet sheet, int firstRowNum) {
    Row titleRow = sheet.getRow(firstRowNum);
    CheckParaUtils.checkObjectValidity(titleRow, logger, String.format("%s中标题行为空", fileName));
    if (titleRow.getPhysicalNumberOfCells() != TemplateFileTitleEnum.values().length) {
      CheckParaUtils.printLogAndThrowException(
          logger,
          String.format(
              "%s中工作表%s标题行不符合规范, 期待有%s参数，实际只有%s个参数",
              fileName,
              sheet.getSheetName(),
              TemplateFileTitleEnum.values().length,
              titleRow.getPhysicalNumberOfCells()));
    }

    for (int cellIndex = 0; cellIndex < titleRow.getPhysicalNumberOfCells(); cellIndex++) {
      Cell titleCell = titleRow.getCell(cellIndex);
      String titleName = getCellValue(titleCell, sheet);
      if (!TemplateFileTitleEnum.getTitleName(cellIndex).getTitle().equals(titleName)) {
        CheckParaUtils.printLogAndThrowException(
            logger,
            String.format(
                "%s中工作表%s标题行不符合规范, 期待第%s列参数是%s，实际是%s",
                fileName,
                sheet.getSheetName(),
                cellIndex + 1,
                TemplateFileTitleEnum.getTitleName(cellIndex).getTitle(),
                titleName));
      }
    }
  }

  /**
   * 获取消息模板基本信息（设备编号、设备名称、设备类型ID、设备类型名称、区域ID、区域名称、资产编号、资产名称、用户ID、用户名称）
   *
   * @param row
   * @return
   */
  private static MsgTemplateDto getDeviceBaseInfoFromRow(
      Sheet sheet, Row row, int rowIndex, List<MsgTemplateDto> devices, String fileName) {
    MsgTemplateDto msgTemplateDto = new MsgTemplateDto();
    msgTemplateDto.setId(ComUtil.getId());
    for (int cellIndex = 0;
        cellIndex <= DcsConstants.TEMPLATE_FILE_DEVICE_BASE_INFO_CELL_INDEX;
        cellIndex++) {
      // 获取待解析的Cell
      Cell valueCell = row.getCell(cellIndex);

      // 获取当前标题Cell限定信息，包括名称、是否可以nul
      TemplateFileTitleEnum currEnum = TemplateFileTitleEnum.getTitleName(cellIndex);

      // 获取解析处理的数值，不论是否是合并单元格
      String value = getCellValue(valueCell, sheet);
      if (StringUtils.isEmpty(value)) {
        if (currEnum != null && currEnum.isNull()) {
          value = null;
        } else {
          CheckParaUtils.printLogAndThrowException(
              logger,
              String.format(
                  "%s中工作表%s第%s行第%s列字段[%s]不能为空",
                  fileName,
                  sheet.getSheetName(),
                  rowIndex + 1,
                  cellIndex + 1,
                  currEnum.getTitle()));
        }
      }

      // 根据列下标，填充字段值
      switch (cellIndex) {
        case 0:
          msgTemplateDto.setDeviceId(value);
          break;
        case 1:
          msgTemplateDto.setDeviceName(value);
          break;
        case 2:
          msgTemplateDto.setDeviceTypeId(value);
        case 3:
          msgTemplateDto.setDeviceTypeName(value);
        case 4:
          msgTemplateDto.setAreaId(value);
        case 5:
          msgTemplateDto.setAreaName(value);
        case 6:
          msgTemplateDto.setAssetId(value);
        case 7:
          msgTemplateDto.setAssetName(value);
        case 8:
          msgTemplateDto.setCustomerId(value);
        default:
          msgTemplateDto.setCustomerName(value);
      }
    }

    // 如果设备基本信息已经在List中，直接返回，否则，加入List中。保证templateId唯一
    if (devices.contains(msgTemplateDto)) {
      CheckParaUtils.printLogAndThrowException(
          logger,
          String.format(
              "%s中工作表%s第%s行模板[%s,%s}]与其他模板ID重复，不允许导入",
              fileName,
              sheet.getSheetName(),
              rowIndex + 1,
              msgTemplateDto.getDeviceId(),
              msgTemplateDto.getDeviceName()));
    }

    // 加入缓存中
    devices.add(msgTemplateDto);

    return msgTemplateDto;
  }

  /**
   * 获取模板中测量参数信息，后面4列是参数信息（参数前缀、参数名称、参数说明、参数类型）
   *
   * @param sheet
   * @param row
   * @param rowIndex
   * @param fileName
   * @param deviceId
   * @return
   */
  private static List<MeasureParaDto> getMeasureParaFrowRow(
      Sheet sheet, Row row, int rowIndex, String fileName, String deviceId) {
    List<MeasureParaDto> measureParaDtoList = new ArrayList<>();

    // step2:获取待解析的Cell
    int cellIndex = DcsConstants.TEMPLATE_FILE_META_CELL_INDEX;
    Cell valueCell = row.getCell(cellIndex);
    // 获取解析处理的数值，不论是否是合并单元格
    String value = getCellValue(valueCell, sheet);
    // 获取当前标题Cell限定信息，包括名称、是否可以nul。如果该字段限定非null、实际为null，抛异常
    TemplateFileTitleEnum currEnum = TemplateFileTitleEnum.getTitleName(cellIndex);
    if (!currEnum.isNull() && StringUtils.isEmpty(value)) {
      CheckParaUtils.printLogAndThrowException(
          logger,
          String.format(
              "%s中工作表%s第%s行第%s列字段[%s]不能为空",
              fileName, sheet.getSheetName(), rowIndex + 1, cellIndex + 1, currEnum.getTitle()));
    }

    // step3:解析metadata字段，填充MeasureParaDto。参考示例："pulse=脉冲, temperature=温度, pressure=压强"
    String[] strings = value.split(Constants.COMMA);
    if (strings == null || strings.length <= 0) {
      CheckParaUtils.printLogAndThrowException(
          logger,
          String.format(
              "%s中工作表%s第%s行第%s列字段[%s]不能为空",
              fileName, sheet.getSheetName(), rowIndex + 1, cellIndex + 1, currEnum.getTitle()));
    }

    for (String tmpStr : strings) {
      String[] tmpStrArray = tmpStr.split(Constants.EQUAL_SIGN);
      if (tmpStrArray.length < 2) {
        CheckParaUtils.printLogAndThrowException(
            logger,
            String.format(
                "%s中工作表%s第%s行第%s列字段[%s]不合法，参考示例：a=b,c=d.",
                fileName, sheet.getSheetName(), rowIndex + 1, cellIndex + 1, currEnum.getTitle()));
      }
      String name = tmpStrArray[0];
      String remark = tmpStrArray[1];
      MeasureParaDto para = new MeasureParaDto();
      para.setId(ComUtil.getId());
      para.setDeviceId(deviceId);
      para.setName(name);
      para.setType(MeasureParaTypeEnum.STRING);
      para.setRemark(remark);
      para.setPrefix(null);

      if (measureParaDtoList.contains(para)) {
        CheckParaUtils.printLogAndThrowException(
            logger,
            String.format(
                "%s中工作表%s第%s行第%s列字段[%s]中参数%s重复",
                fileName,
                sheet.getSheetName(),
                rowIndex + 1,
                cellIndex + 1,
                currEnum.getTitle(),
                name));
      }
      measureParaDtoList.add(para);
    }

    return measureParaDtoList;
  }

  /**
   * 判断row是否为空行
   *
   * @param row
   * @return
   */
  private static boolean isRowEmpty(Row row) {
    if (row == null) {
      return true;
    }

    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
      Cell cell = row.getCell(c);
      if (cell != null && cell.getCellTypeEnum() != CellType.BLANK) {
        return false;
      }
    }

    return true;
  }

  /**
   * 从inputStream中获取WorkBook
   *
   * @param inputStream
   * @return
   */
  private static Workbook getWorkBook(String fileName, InputStream inputStream) {
    Workbook workBook = null;
    try {
      if (fileName.endsWith(Constants.EXCEL_2007_FILE_SUFFIX)) {
        workBook = new XSSFWorkbook(inputStream);
      } else if (fileName.endsWith(Constants.EXCEL_2003_FILE_SUFFIX)) {
        workBook = new HSSFWorkbook(inputStream);
      } else {
        CheckParaUtils.printLogAndThrowException(
            logger, String.format("模板文件[%s]格式不支持，仅支持.xls、.xlsx.", fileName));
      }
    } catch (Exception e) {
      CheckParaUtils.printLogAndThrowException(logger, e.getMessage());
    }

    return workBook;
  }

  /**
   * 根据模板文件后缀获取WorkBook
   *
   * @param fileName
   * @return
   */
  private static Workbook getWorkBookFromFile(String fileName) {
    // step1:判断文件是否存在
    File file = new File(fileName);
    if (!file.exists()) {
      CheckParaUtils.printLogAndThrowException(
          logger, String.format("File[%s] doesn't exist.", fileName));
    }

    // step2:获取输入流
    InputStream inputStream = null;
    try {
      inputStream = new BufferedInputStream(new FileInputStream(file));
    } catch (Exception e) {
      CheckParaUtils.printLogAndThrowException(
          logger, String.format("File[%s] get inputStream failed,ErrMsg=%s", fileName, e));
    }

    // step3:获取WorkBook
    Workbook workBook = null;
    try {
      if (fileName.endsWith(Constants.EXCEL_2007_FILE_SUFFIX)) {
        workBook = new XSSFWorkbook(inputStream);
      } else if (fileName.endsWith(Constants.EXCEL_2003_FILE_SUFFIX)) {
        workBook = new HSSFWorkbook(inputStream);
      } else {
        CheckParaUtils.printLogAndThrowException(
            logger, String.format("Template file type[%s] unsupport.", fileName));
      }
    } catch (Exception e) {
      CheckParaUtils.printLogAndThrowException(logger, e.getMessage());
    }

    return workBook;
  }

  /**
   * 获取工作表
   *
   * @param workBook
   * @param index
   * @return
   */
  private static Sheet getSheetByIndex(Workbook workBook, int index) {
    // step2:定义工作表
    Sheet sheet = workBook.getSheetAt(index);
    if (sheet == null) {
      CheckParaUtils.printLogAndThrowException(
          logger, String.format("Get sheet[%s] failed.", index));
    }

    return sheet;
  }

  /**
   * 获取当前单元格的值
   *
   * @param cell
   * @return
   */
  private static String getCurrentCellValue(Cell cell) {
    if (cell == null) {
      return null;
    }

    if (cell.getCellTypeEnum() == CellType.BLANK) {
      return null;
    }

    return cell.getStringCellValue();
  }

  /**
   * 获取工作表中所有合并单元格信息
   *
   * @param sheet
   */
  private static void getCombineCell(Sheet sheet) {
    List<CellRangeAddress> list = new ArrayList<>();

    // step1:每次读取Sheet时清空列表
    cellRangeAddresses.clear();

    // step2:遍历所有的合并单元格，保存在cellRangeAddresses中
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
      // 获得合并单元格保存进list中
      CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
      cellRangeAddresses.add(cellRangeAddress);
    }
  }

  /**
   * 判断单元格是否为合并单元格，是的话则将单元格的值返回
   *
   * @param cell 需要判断的单元格
   * @param sheet sheet
   */
  private static String getCellValue(Cell cell, Sheet sheet) {
    // 单元格为null时返回null
    if (cell == null) {
      return null;
    }

    int firstColIndex;
    int lastColIndex;
    int firstRowIndex;
    int lastRowIndex;
    String cellValue = null;
    boolean isCombinedCell = false;
    for (CellRangeAddress tmpCellRangeAddress : cellRangeAddresses) {
      // 获得合并单元格的起始行, 结束行, 起始列, 结束列
      firstColIndex = tmpCellRangeAddress.getFirstColumn();
      lastColIndex = tmpCellRangeAddress.getLastColumn();
      firstRowIndex = tmpCellRangeAddress.getFirstRow();
      lastRowIndex = tmpCellRangeAddress.getLastRow();
      if (cell.getRowIndex() >= firstRowIndex && cell.getRowIndex() <= lastRowIndex) {
        if (cell.getColumnIndex() >= firstColIndex && cell.getColumnIndex() <= lastColIndex) {
          Row firstRow = sheet.getRow(firstRowIndex);
          Cell firstCell = firstRow.getCell(firstColIndex);
          cellValue = getCurrentCellValue(firstCell);

          isCombinedCell = true;

          break;
        }
      }
    }

    if (!isCombinedCell) {
      cellValue = getCurrentCellValue(cell);
    }

    return cellValue;
  }

  /**
   * 检查excel表头是否标准模板
   *
   * @param is
   * @return
   */
  public static boolean checkStandardTemplate(InputStream is) {
    try {
      Workbook wb =
          StreamingReader.builder()
              .rowCacheSize(10) // 缓存到内存中的行数，默认是10
              .bufferSize(8192) // 读取资源时，缓存到内存的字节大小，默认是1024
              .open(is); // 打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件

      List<String> titleList = new ArrayList<>();
      if (wb != null) {
        Sheet sheet = wb.getSheetAt(0);
        // 遍历所有的行
        for (Row row : sheet) {
          // 遍历所有的列
          for (Cell cell : row) {
            titleList.add(cell.getStringCellValue());
          }

          // 仅获取第一行表头
          if (row.getRowNum() >= 0) {
            break;
          }
        }
      }

      if (titleList.size() == 4
          && titleList.get(0).equals("序号")
          && titleList.get(1).equals("字段名称")
          && titleList.get(2).equals("类型")
          && titleList.get(3).equals("描述")) {
        return true;
      }
    } catch (Exception e) {
      logger.error(e.getMessage(), e);
    } finally {
      if (is != null) {
        try {
          is.close();
        } catch (Exception e) {
          logger.error(e.getMessage(), e);
        }
      }
    }
    return false;
  }

  /**
   * 解析文件模板中数据
   *
   * @param is
   * @return
   */
  public static List<TemplateFieldDTO> parseStandardTemplate(InputStream is) {
    List<TemplateFieldDTO> fieldList = new ArrayList<>();
    try {
      Workbook wb =
          StreamingReader.builder()
              .rowCacheSize(10) // 缓存到内存中的行数，默认是10
              .bufferSize(8192) // 读取资源时，缓存到内存的字节大小，默认是1024
              .open(is); // 打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件

      if (wb != null) {
        Sheet sheet = wb.getSheetAt(0);
        // 遍历所有的行
        for (Row row : sheet) {
          if (row.getRowNum() == 0) {
            continue;
          }

          List<String> dataList = new ArrayList<>();
          // 遍历所有的列
          for (Cell cell : row) {
            dataList.add(cell.getStringCellValue());
          }

          if (dataList.size() == 4) {
            TemplateFieldDTO fieldDTO = new TemplateFieldDTO();
            fieldDTO.setOrderBy(Integer.parseInt(dataList.get(0)));
            fieldDTO.setName(dataList.get(1));
            fieldDTO.setType(dataList.get(2));
            fieldDTO.setRemark(dataList.get(3));
            fieldList.add(fieldDTO);
          }
        }
      }
    } catch (Exception e) {
      logger.error(e.getMessage(), e);
    } finally {
      if (is != null) {
        try {
          is.close();
        } catch (Exception e) {
          logger.error(e.getMessage(), e);
        }
      }
    }
    return fieldList;
  }

  /**
   * 获取excel表头
   *
   * @param is
   * @param suffix
   * @return
   */
  public static String getHeaderByStartLine(InputStream is, String suffix, int startLine) {
    String header = "";
    if (Constants.EXCEL_2003_FILE_SUFFIX.equals(suffix)) {
      header = getExcel2003HeaderByStartLine(is, startLine);
    } else if (Constants.EXCEL_2007_FILE_SUFFIX.equals(suffix)) {
      header = getExcel2007HeaderByStartLine(is, startLine);
    } else {
      throw new BusinessException("不支持的文件类型:" + suffix);
    }
    return header;
  }

  /**
   * 获取excel2003表头
   *
   * @param is
   * @return
   */
  public static String getExcel2003HeaderByStartLine(InputStream is, int startLine) {
    String buffer = "";
    try {
      Sheet sheet = null;
      Row row = null;
      String cellData = null;
      Workbook wb = new HSSFWorkbook(is);

      if (wb != null) {
        // 获取第一个sheet
        sheet = wb.getSheetAt(0);
        // 获取第一行,仅获取表头行
        row = sheet.getRow(0);
        // 获取最大列数
        int colnum = row.getPhysicalNumberOfCells();
        for (int j = 0; j < colnum; j++) {
          cellData = (String) getCellFormatValue(row.getCell(j));
          buffer += cellData;
        }
        buffer = buffer.substring(0, buffer.lastIndexOf(",")).toString();
      }
    } catch (Exception e) {
      logger.error(e.getMessage(), e);
    } finally {
      if (is != null) {
        try {
          is.close();
        } catch (Exception e) {
          logger.error(e.getMessage(), e);
        }
      }
    }
    return buffer;
  }

  /**
   * 获取excel2007表头
   *
   * @param is
   * @return
   */
  public static String getExcel2007HeaderByStartLine(InputStream is, int startLine) {
    String buffer = "";
    try {
      Workbook wb =
          StreamingReader.builder()
              .rowCacheSize(10) // 缓存到内存中的行数，默认是10
              .bufferSize(8192) // 读取资源时，缓存到内存的字节大小，默认是1024
              .open(is); // 打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
      Sheet sheet = wb.getSheetAt(0);

      if (wb != null) {
        // 遍历所有的行
        for (Row row : sheet) {
          // 仅获取第一行表头
          if (row.getRowNum() == startLine - 1) {
            // 遍历所有的列
            for (Cell cell : row) {
              buffer += cell.getStringCellValue() + ",";
            }
            break;
          } else {
            continue;
          }
        }

        buffer = buffer.substring(0, buffer.lastIndexOf(",")).toString();
      }
    } catch (Exception e) {
      logger.error(e.getMessage(), e);
    } finally {
      if (is != null) {
        try {
          is.close();
        } catch (Exception e) {
          logger.error(e.getMessage(), e);
        }
      }
    }
    return buffer;
  }

  /**
   * 获取单元格的值
   *
   * @param cell
   * @return
   */
  public static Object getCellFormatValue(Cell cell) {
    Object cellValue = null;
    if (cell != null) {
      // 判断cell类型
      switch (cell.getCellType()) {
        case NUMERIC:
          {
            cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";
            break;
          }
        case FORMULA:
          {
            // 判断cell是否为日期格式
            if (DateUtil.isCellDateFormatted(cell)) {
              // 转换为日期格式YYYY-mm-dd
              cellValue = String.valueOf(cell.getDateCellValue()).replaceAll("\n", " ") + ",";
            } else {
              // 数字
              cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";
            }
            break;
          }
        case STRING:
          {
            cellValue = cell.getRichStringCellValue().getString().replaceAll("\n", " ") + ",";
            break;
          }
        default:
          cellValue = "";
      }
    } else {
      cellValue = "";
    }
    return cellValue;
  }

  public static void main(String[] args) throws Exception {
    String tmp = "C:\\Users\\admin\\Desktop\\项目\\8、流式数据采集\\5、测试\\2、后台测试\\dbexport2.xlsx";
    List<MsgTemplateDto> devices = null;
    devices = parseDeviceInfo(tmp);
    System.out.println(devices);
  }
}
